# -*- coding: utf-8 -*-
__author__ = 'lztkdr'
__date__ = '2019/7/19 10:07'

import pymysql
from twisted.enterprise import adbapi

# conn_config = {
# 	"host":'localhost',
# 	"port":3306,
# 	"user":'root',
# 	"password":'123456',
# 	"database":'world',
# 	"charset":'utf8',
# 	"unix_socket":None
# }

class sql_helper(object):

	def __init__(self,database,host="localhost",port=3306,user="root",password="123456",charset="utf8"):
		"""
		初始化Helper对象
		:param database:数据库
		:param host:连接主机
		:param port:端口号
		:param user:用户名
		:param password:密码
		:param charset:字符集
		"""
		self.__host = host
		self.__port = port
		self.__user = user
		self.__password = password
		self.__database = database
		self.__charset = charset

		params = dict(
			host=self.__host,
			user=self.__port,
			password=self.__password,
			db=self.__database,
			charset=self.__charset,
			cursorclass=pymysql.cursors.DictCursor # 指定 curosr 类型
		)
		# 使用Twisted中的adbapi获取数据库连接池对象
		self.dbpool = adbapi.ConnectionPool('pymysql', **params)

	def __get_conn_cursor(self):
		"""
		创建连接，获取游标
		:return:
		"""
		conn = pymysql.connect(
			host = self.__host,
			port = self.__port,
			user = self.__user,
			passwd = self.__password,
			db = self.__database,
			charset = self.__charset
		)
		# 创建游标对象
		cursor = conn.cursor()
		return (conn,cursor)

	def __close(self,conn,cursor):
		"""
		关闭游标，关闭连接
		:param conn:
		:param cursor:
		:return:
		"""
		cursor.close()
		conn.close()

	def fetchall(self, sql,args = None):
		"""
		查询所有数据
		:param sql:
		:param args:
		:return:
		"""
		try:
			(conn, cursor) = self.__get_conn_cursor()
			cursor.execute(sql,args)
			results = cursor.fetchall()
			return results
		finally:
			self.__close(conn,cursor)

	def fetchone(self, sql,args = None):
		"""
		查询单个值
		:param sql:
		:return:
		"""
		try:
			(conn, cursor) = self.__get_conn_cursor()
			cursor.execute(sql,args)
			results = cursor.fetchone()
			return results
		finally:
			self.__close(conn,cursor)

	def execute(self,sql,args = None):
		"""
		执行命令
		:param sql:
		:param args:
		:return:
		"""
		if not sql:
			return -1
		try:
			effectRow = 0
			(conn, cursor) = self.__get_conn_cursor()
			cursor.execute(sql,args)
			effectRow += cursor.rowcount
			conn.commit()
			return effectRow
		except Exception as ex:
			conn.rollback()
			raise ex
		finally:
			self.__close(conn, cursor)

	def executemany(self,sql,args = None):
		"""
		传入sql语句，以及多组 对应值
		:param sql:
		:return:受影响行数
		"""
		if not sql:
			return -1
		try:
			effectRow = 0
			(conn, cursor) = self.__get_conn_cursor()
			cursor.executemany(sql,args)
			effectRow += cursor.rowcount
			conn.commit()
			return effectRow
		except Exception as ex:
			conn.rollback()
			raise ex
		finally:
			self.__close(conn, cursor)

	def __exec_insert(self,sql,args):
		"""
		插入数据，返回 lastrowid
		:param sql:
		:param args:
		:return:
		"""
		if not sql:
			return -1
		try:
			(conn, cursor) = self.__get_conn_cursor()
			cursor.execute(sql,args)
			conn.commit()
			return cursor.lastrowid
		except Exception as ex:
			conn.rollback()
			raise ex
		finally:
			self.__close(conn, cursor)

	def __get_insert_sql(self,table_name,**kwargs):
			"""
			根据表名 以及 键值对 生成 insert 语句
			:param table_name:表名
			:param kwargs:键值对
			:return: （insert_sql,列表值）
			"""
			insert_sql = "insert into {table_name}({column_names})values({column_values})"
		# if type(kwargs).__name__ == "dict":
			insert_sql = insert_sql.format(table_name=table_name, column_names=",".join(list(kwargs.keys())),
		                               column_values=("%s," * len(kwargs)).strip(","))
			return (insert_sql,list(kwargs.values()))

	def insert(self,table_name,**kwargs):
			"""
			根据表名，键值对 插入数据
			:param table_name:数据库表名
			:param kwargs:键值对
			:return:默认返回自增值(如果有自增)
			"""
			(insert_sql,args) = self.__get_insert_sql(table_name,**kwargs)
			return self.__exec_insert(insert_sql,args)
		# else:
		# 	raise ValueError(f"未传入字典类型对象。{kwargs}")

	def insert_dict(self, table_name, dict):
		"""
		根据表名，字典 插入数据
		:param table_name:数据库表名
		:param kwargs:字典
		:return:默认返回自增值(如果有自增)
		"""
		return self.insert(table_name,**dict)

	def insert_obj(self,obj):
		"""
		插入对象
		:param obj:对象值，对象名称必须与数据库表名称相同
		:return:默认返回自增值(如果有自增)
		"""
		obj_dict = obj.__dict__
		# 判断是否 是 scrapy.Item 对象
		temp_dict = obj_dict.get("_values", None)
		if temp_dict:
			obj_dict = temp_dict
		table_name = obj.__class__.__name__
		return self.insert_dict(table_name,obj_dict)

	############################ 异步API BEGIN #####################################

	def aync_insert_obj(self,obj):
		# 使用数据库连接池对象进行数据库操作,自动传递cursor对象到第一个参数
		query = self.dbpool.runInteraction(self.__do_insert, obj)
		query.addCallback(self.__on_callback, obj, None)
		# 设置出错时的回调方法,自动传递出错消息对象failure到第一个参数
		query.addErrback(self.__on_error, obj, None)


	def __do_insert(self, cursor, obj):
		try:
			obj_dict = obj.__dict__
			# 判断是否 是 scrapy.Item 对象
			temp_dict = obj_dict.get("_values", None)
			if temp_dict:
				obj_dict = temp_dict
			table_name = obj.__class__.__name__
			(insertSql, values) = self.__get_insert_sql(table_name, **obj_dict)
			print(f"{insertSql}===={values}")
			cursor.execute(insertSql, values)
		except Exception as ex:
			print(ex)

	def __on_callback(self, failure, *args, **kw):
		print(f"插入出错：{failure}")

	def __on_error(self, errback, *args, **kw):
		print(f"插入完成：{args}")

	############################ 异步API END #####################################


	############################## 表结构查询 BEGIN ##############################
	def is_exist_table(self,table_name):
		"""
		查询数据库中 是否存在 某张表
		:param table_name:表名称
		:return:返回 Boolean 类型
		"""
		sel_sql = f"select table_name from INFORMATION_SCHEMA.TABLES where lower(table_type)='base table' and  lower(table_schema) = '{self.__database.lower()}' and lower(table_name) ='{str(table_name).lower()}'"
		res = self.fetchone(sel_sql)
		return res != None

	def is_exist_column(self,table_name,column_name):
		"""
		查询 列名 是否在 某张表 中
		:param table_name:表名
		:param column_name:列名
		:return:返回 Boolean 类型
		"""
		sel_sql = f"select column_name from information_schema.COLUMNS where lower(table_schema) = '{self.__database.lower()}' and lower(table_name) = '{table_name.lower()}' and lower(column_name)='{str(column_name).lower()}'"
		res = self.fetchone(sel_sql)
		return res != None

	def insert_table_not_exist_create(self, obj):
		obj_dict = obj.__dict__
		# 判断是否 是 scrapy.Item 对象
		temp_dict =  obj_dict.get("_values",None)
		if temp_dict:
			obj_dict = temp_dict
		table_name = obj.__class__.__name__
		if not self.is_exist_table(table_name):
			create_sql = "create table {table_name}({column_detail})"
			create_sql = create_sql.format(table_name = table_name, column_detail=",".join([ key + " text " for key in list(obj_dict.keys())]))
			print(create_sql)
			self.execute(create_sql)
		self.insert_dict(table_name,obj_dict)
############################## 表结构查询 END  ##############################



